using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using DAL;
using System.Data;


namespace Métiers
{

    public class Connection
    {
        public static string chaineConnexion = string.Format("SERVER=localhost; DATABASE=catalogue; UID=root; PASSWORD=;");

        public static void ouvertureBd()
        {
            try
            {
                PasserelleMySql.ouvertureConnexion(chaineConnexion);
            }
            catch (Exception msg)
            {
                // Message sur la nature de l'erreur
                Console.WriteLine(msg.ToString());
                throw;
            }
        }

        public static void fermetureBd()
        {
            PasserelleMySql.fermetureConnexion();
        }



    }






    public class Accueil
    {


        public static DataTable listeGroupe()
        {
            string sql = "SELECT DISTINCT fGroupe FROM famille ORDER BY fGroupe ASC;";
            return PasserelleMySql.executionRequete(sql);
        }

        public static DataTable listeFamille(string groupe)
        {
            string sql = "SELECT fLibelle FROM famille WHERE fGroupe LIKE '%"+groupe+"%' ORDER BY fLibelle ASC";
            return PasserelleMySql.executionRequete(sql);
        }

        public static DataTable barreRecherche(string nom)
        {
            string sql = "SELECT mNomCommercial AS Nom FROM medicament where mNomCommercial LIKE '%"+nom+"%'ORDER BY mNomCommercial ASC;";
            return PasserelleMySql.executionRequete(sql);
        }
    }








    public class Login
    {

        public static DataTable verifLogin(string identifiant, string motDePasse)
        {
            string sql = "SELECT COUNT(id) FROM utilisateurs WHERE mot_de_passe LIKE SHA2('"+motDePasse+"', 256) and identifiant LIKE '" + identifiant + "';";
            return PasserelleMySql.executionRequete(sql);
        }


    }




    public class FamilleMedicament
    {

        public static DataTable listeMedicament(string famille)
        {
            string sql = "SELECT mNomCommercial AS Nom,mEffets AS Effets, mContreIndications AS ContresIndications FROM medicament INNER JOIN famille ON famille.fCode=medicament.fCode WHERE fLibelle LIKE '%" + famille + "%';";
            return PasserelleMySql.executionRequete(sql);
        }

        public static DataTable listeMedicamentBarreRecherche(string nom, string groupe, string famille)
        {
            string sql = "SELECT mNomCommercial AS Nom,mEffets AS Effets, mContreIndications AS ContresIndications FROM medicament INNER JOIN famille ON famille.fCode=medicament.fCode WHERE mNomCommercial LIKE '%" + nom + "%' AND fGroupe LIKE '%"+groupe+"%' AND fLibelle LIKE '%" + famille + "%';";
            return PasserelleMySql.executionRequete(sql);
        }
    }







    public class Medicament
    {

        public static DataTable medicament(string nomMedicament)
        {
            string sql = "SELECT mComposition, mEffets, mContreIndications, mDepotLegal FROM medicament WHERE mNomCommercial LIKE '%"+nomMedicament+"%';";
            return PasserelleMySql.executionRequete(sql);
        }

        public static DataTable observations(string nomMedicament)
        {
            string sql = "SELECT oDate AS Date, oRemarque AS Observations, CONCAT(pNom,' ',pPrenom, ' - ',pVille) AS Praticien      FROM observation     INNER JOIN medicament ON medicament.mDepotLegal=observation.mDepotLegal    INNER JOIN praticien ON praticien.pNum=observation.pNum      WHERE mNomCommercial LIKE '%" + nomMedicament + "%';";
            return PasserelleMySql.executionRequete(sql);
        }

        public static DataTable groupeFamilleMedicament(string nomMedicament)
        {
            string sql = "SELECT fGroupe, fLibelle FROM medicament INNER JOIN famille ON famille.fCode=medicament.fCode WHERE mNomCommercial LIKE '%"+nomMedicament+"%';";
            return PasserelleMySql.executionRequete(sql);
        }

        public static DataTable nomMedicamentGroupe(string groupe)
        {
            string sql = "SELECT mNomCommercial FROM medicament INNER JOIN famille ON famille.fCode=medicament.fCode WHERE fGroupe LIKE '%" + groupe + "%';";
            return PasserelleMySql.executionRequete(sql);
        }

        public static DataTable nomMedicamentGroupeFamille(string groupe, string famille)
        {
            string sql = "SELECT mNomCommercial FROM medicament INNER JOIN famille ON famille.fCode=medicament.fCode WHERE fGroupe LIKE '"+groupe+"' and fLibelle LIKE '"+famille+"';";
            return PasserelleMySql.executionRequete(sql);
        }
    }







    public class Observation
    {
        public static DataTable praticiens()
        {
            string sql = "SELECT CONCAT(pNom,' ',pPrenom) FROM praticien ORDER BY pNom ASC;";
            return PasserelleMySql.executionRequete(sql);
        }

        public static DataTable pNumPraticiens(string pNomPrenom)
        {
            string sql = "SELECT pNum FROM praticien WHERE CONCAT(pNom,' ',pPrenom) LIKE '%"+pNomPrenom+ "%' ORDER BY pNom ASC;";
            return PasserelleMySql.executionRequete(sql);
        }

        public static void nouvelleObservation(int oNum,int pNum, string oRemarque, string mDepotLegal)
        {
            DateTime now = DateTime.Now;
            string date = now.ToString("yyyy-MM-dd");

            string sql = "INSERT INTO observation (oNum,pNum, mDepotLegal, oRemarque, oDate) VALUES ('"+oNum+"','"+pNum+"','"+mDepotLegal+"','"+oRemarque+"','"+date+"')";
            PasserelleMySql.executionMaj(sql);
        }

        public static DataTable oNum()
        {
            string sql = "SELECT MAX(oNum) + 1 AS oNum FROM observation;";
            return PasserelleMySql.executionRequete(sql);
        }

    }
}